package dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;

import entity.ContaBancoENT;
import entity.SaldoENT;
import entity.UsuarioENT;

public class SaldoDAO extends GenericDAO {

	@SuppressWarnings("unchecked")
	public List<SaldoENT> getSaldos(int id_usuario) {
		
		try {
			query = sessao.getNamedQuery("getSaldos");
			query.setParameter("id_usuario", id_usuario);
			if(query.list().isEmpty()){
				return new ArrayList<SaldoENT>();
				//return new UsuarioENT;
			}else{
				return query.list();
			}			
		} catch (HibernateException e) {
			System.out.println("Erro encontrado:"+getClass().toString()+" Mesagem de Erro: "+e.toString());			 
			return new ArrayList<SaldoENT>();
		}
	}
	
	public SaldoENT getSaldoMes(UsuarioENT usuario, Date data, ContaBancoENT contaBanco) {
		
		try {
			Calendar cal = Calendar.getInstance(); 
			cal.setTime(data);
			
			int mes = cal.get(Calendar.MONTH);
			String ano = ""+cal.get(Calendar.YEAR);
			cal.add(Calendar.MONTH, -3);  
			int lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH);  
			String dataInicial =  "01"+"/"+mes+"/"+ano.substring(2,4)+" 00:00:00";
			String dataFinal = lastDay+"/"+mes+"/"+ano.substring(2,4)+" 23:59:59";
			
			String sql = "select s.id                                                                  "
						+ "	      ,s.valor_saldo                                                       "
						+ "	      ,max(s.data_saldo) as data_saldo                                     "
						+ "	      ,s.descricao                                                         "
						+ "	      ,s.id_conta_banco                                                    "
						+ "	      ,s.id_usuario                                                        "
						+ "from   gff.saldo s                                                          "
						+ "where  s.id_usuario = :id_usuario                                           "
						+ "and	  s.id_conta_banco = :id_conta_banco                                   "
						+ "and	  s.data_saldo between date_format(:dataInicial,'%d/%m/%y %T')         "
						+ "		 			       and date_format(:dataFinal,'%d/%m/%y %T')           ";
			
			query = sessao.createSQLQuery(sql).addEntity("saldo",SaldoENT.class);
			query.setParameter("id_conta_banco", contaBanco.getId());
			query.setParameter("id_usuario", usuario);
			query.setParameter("dataInicial", dataInicial);
			query.setParameter("dataFinal", dataFinal);
			
			
			if(query.list().isEmpty()){
				return new SaldoENT();
				//return new UsuarioENT;
			}else{
				return (SaldoENT) query.list().get(0);
			}			
		} catch (HibernateException e) {
			System.out.println("Erro encontrado:"+getClass().toString()+" Mesagem de Erro: "+e.toString());			 
			return new SaldoENT();
		}
	}
	
	public SaldoDAO() {
		// TODO Auto-generated constructor stub
	}

}
